package smart.wallet.db;


import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHelper extends SQLiteOpenHelper {
	
	private static final String DATABASE_NAME = "smartwallet";
	private static final int DATABASE_VERSION = 1;
	public static final int CREDIT = 1;
	public static final int DEBT = 2;
	
	
	// Register table
	public static final String REGISTER_TABLE = "register";
	public static final String REGISTER_ID = "_id";
	public static final String REGISTER_ACCOUNT_ID = "account_id";
	public static final String REGISTER_VALUE = "value";
	public static final String REGISTER_CATEGORY_ID = "category_id";
	public static final String REGISTER_OBS = "obs";
	public static final String REGISTER_DATE = "date";
	
	// Account table
	public static final String ACCOUNT_TABLE = "account";
	public static final String ACCOUNT_ID = "_id";
	public static final String ACCOUNT_NAME = "name";
	
	// Category table
	public static final String CATEGORY_TABLE = "category";
	public static final String CATEGORY_ID = "_id";
	public static final String CATEGORY_ACCOUNT_ID = "account_id";
	public static final String CATEGORY_NAME = "name";
	public static final String CATEGORY_IMAGE = "image";
	public static final String CATEGORY_COLOR = "color";
	public static final String CATEGORY_TYPE = "type";
	public static final String CATEGORY_VALID = "valid";
	
	
	// Advice table
	public static final String ADVICE_TABLE = "advice";
	public static final String ADVICE_ID = "_id";
	public static final String ADVICE_NAME = "name";
	public static final String ADVICE_RULE = "rule";
	public static final String ADVICE_STATE = "state";
	
	
	// Balance table
	public static final String BALANCE_TABLE = "balance";
	public static final String BALANCE_ID = "_id";
	public static final String BALANCE_ACCOUNT_ID = "account_id";
	public static final String BALANCE_DATE = "date";
	public static final String BALANCE_VALUE = "value";
	
	// Account Advice relation table
	public static final String ACCOUNTADVICE_TABLE = "accountadvice";
	public static final String ACCOUNTADVICE_ID = "_id";
	public static final String ACCOUNTADVICE_ACCOUNT_ID = "account_id";
	public static final String ACCOUNTADVICE_ADVICE_ID = "advice_id";
	
	
	private static final String ACCOUNT_CREATE = "create table " + ACCOUNT_TABLE + " (" 
			+ ACCOUNT_ID +" integer primary key autoincrement, "
			+ ACCOUNT_NAME + " text);";
	
	private static final String CATEGORY_CREATE = "create table " + CATEGORY_TABLE + " (" 
			+ CATEGORY_ID +" integer primary key autoincrement, "
			+ CATEGORY_NAME + " text, "
			+ CATEGORY_IMAGE + " integer, "
			+ CATEGORY_COLOR + " text, "
			+ CATEGORY_TYPE + " integer, "
			+ CATEGORY_VALID + " integer, "
			+ CATEGORY_ACCOUNT_ID + " integer, FOREIGN KEY("+CATEGORY_ACCOUNT_ID+") REFERENCES "+ ACCOUNT_TABLE +"("+ACCOUNT_ID+")); " ;
	
	private static final String REGISTER_CREATE = "create table " + REGISTER_TABLE + " (" 
			+ REGISTER_ID +" integer primary key autoincrement, " 
			+ REGISTER_VALUE + " real, " 
			+ REGISTER_OBS + " text, "
			+ REGISTER_DATE + " text, "
			+ REGISTER_ACCOUNT_ID + " integer not null, " 
			+ REGISTER_CATEGORY_ID + " integer, " 
			+ "FOREIGN KEY("+REGISTER_ACCOUNT_ID+") REFERENCES "+ACCOUNT_TABLE+"("+ACCOUNT_ID+"), "
			+ "FOREIGN KEY("+REGISTER_CATEGORY_ID+") REFERENCES "+ CATEGORY_TABLE +"("+CATEGORY_ID+")); ";
	
	private static final String ADVICE_CREATE = "create table " + ADVICE_TABLE + " (" 
			+ ADVICE_ID +" integer primary key autoincrement, "
			+ ADVICE_NAME + " text," 
			+ ADVICE_RULE + "text," 
			+ ADVICE_STATE + " text);";
	
	private static final String BALANCE_CREATE = "create table " + BALANCE_TABLE + " (" 
			+ BALANCE_ID +" integer primary key autoincrement, "
			+ BALANCE_DATE + "text," 
			+ BALANCE_VALUE + " real,"
			+ BALANCE_ACCOUNT_ID + " integer, FOREIGN KEY("+BALANCE_ACCOUNT_ID+") REFERENCES "+ ACCOUNT_TABLE +"("+ACCOUNT_ID+")); " ;
	
	private static final String ACCOUNTADVICE_CREATE = "create table " + ACCOUNTADVICE_TABLE + " (" 
			+ ACCOUNTADVICE_ID +" integer primary key autoincrement, "
			+ ACCOUNTADVICE_ACCOUNT_ID + " integer, "  
			+ ACCOUNTADVICE_ADVICE_ID + " integer, " 
			+ "FOREIGN KEY("+ACCOUNTADVICE_ACCOUNT_ID+") REFERENCES "+ ACCOUNT_TABLE +"("+ACCOUNT_ID+"),"
			+ "FOREIGN KEY("+ACCOUNTADVICE_ADVICE_ID+") REFERENCES "+ ADVICE_TABLE +"("+ADVICE_ID+"));";
	

	public DBHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// Method is called during creation of the database
	@Override
	public void onCreate(SQLiteDatabase database) {
		database.execSQL(ACCOUNT_CREATE);
		database.execSQL(CATEGORY_CREATE);
		database.execSQL(REGISTER_CREATE);
		database.execSQL(ADVICE_CREATE);
		database.execSQL(BALANCE_CREATE);
		database.execSQL(ACCOUNTADVICE_CREATE);
	}

	// Method is called during an upgrade of the database, e.g. if you increase
	// the database version
	@Override
	public void onUpgrade(SQLiteDatabase database, int oldVersion,
			int newVersion) {
		Log.w(DBHelper.class.getName(),
				"Upgrading database from version " + oldVersion + " to "
						+ newVersion + ", which will destroy all old data");
		database.execSQL("DROP TABLE IF EXISTS "+ ACCOUNT_TABLE);
		database.execSQL("DROP TABLE IF EXISTS "+ BALANCE_TABLE);
		database.execSQL("DROP TABLE IF EXISTS "+ CATEGORY_TABLE);
		database.execSQL("DROP TABLE IF EXISTS "+ REGISTER_TABLE);
		database.execSQL("DROP TABLE IF EXISTS "+ ADVICE_TABLE);
		database.execSQL("DROP TABLE IF EXISTS "+ ACCOUNTADVICE_TABLE);
		
		onCreate(database);
	}
}